using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace LiveStock.dataAccess
{
    /// <summary>
    /// Data Access Layer class for Users table
    /// </summary>
    public class UsersData
    {
        private UsersData() { }

        public static int UserInsert(Users.UsersRow ur)
        {
            int affectedRows = 0;
            using (SqlCommand cmd = DBManager.GetCommand("UserInsert"))            
            {
                cmd.Parameters.AddWithValue("UserName", ur.UserName);
                cmd.Parameters.AddWithValue("Password", ur.Password);
                cmd.Parameters.AddWithValue("FullName", ur.FullName);
                cmd.Parameters.AddWithValue("UserType", ur.UserType);
                affectedRows = DBManager.ExecuteNonQuery(cmd);
            }
            return affectedRows;
        }

        public static Users.UsersDataTable GetUsers()
        {
            Users.UsersDataTable udt = new Users.UsersDataTable();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users ORDER BY UserType"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(udt, cmd);
            }
            return udt;
        }

        public static Users.UsersDataTable GetUserByID(int userID)
        {
            Users.UsersDataTable udt = new Users.UsersDataTable();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE UserID = " + userID))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(udt, cmd);
            }
            return udt;

        }

        public static int setSavedRecommendationLimit(int userID, int limit)
        {
            int ar = 0;
            using (SqlCommand cmd = DBManager.GetCommand("UserSetSavedRecommendationLimit"))
            {
                cmd.Parameters.AddWithValue("UserID", userID);
                cmd.Parameters.AddWithValue("SavedRecommendationLimit", limit);
                ar = DBManager.ExecuteNonQuery(cmd);
            }
            //perform multi-dimensional hyperactive semblence retrieval
            Companies.CompaniesDataTable bob1 = CompaniesData.CompaniesGetByUserID(userID);
            foreach (Companies.CompaniesRow bob2 in bob1)
            {
                Recommendations.RecommendationsDataTable bob3 = RecommendationsData.RecommendationsGetByCompanyID(bob2.CompanyID);
                if (bob3.Rows.Count > limit)
                {
                    int bob4 = bob3.Rows.Count - limit;
                    Recommendations.RecommendationsRow[] bob5 = (Recommendations.RecommendationsRow[])bob3.Select("", bob3.RecommendationDateColumn.ColumnName + " asc");
                    for (int i = 0; i < bob4; i++)
                    {
                        RecommendationsData.RecommendationDeleteByID(bob5[i].RecommendationID);
                    }
                }
            }
            return ar;
        }

        public static Users.UsersDataTable UserLogin(string userName, string password)
        {
            Users.UsersDataTable udt = new Users.UsersDataTable();
            using (SqlCommand cmd = DBManager.GetCommand("UserLogin"))
            {
                cmd.Parameters.AddWithValue("UserName", userName);
                cmd.Parameters.AddWithValue("Password", password);
                DBManager.FillDataTable(udt, cmd);
            }
            return udt;
        }

        public static Users.UsersDataTable AdminCheck()
        {
            Users.UsersDataTable udt = new Users.UsersDataTable();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE UserType = 0"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(udt, cmd);                
            }
            return udt;
        }

        public static int UserDeleteByID(int userID)
        {
            int affectedRows = 0;
            using (SqlCommand cmd = DBManager.GetCommand("UserDeleteByID"))
            {
                cmd.Parameters.AddWithValue("UserID", userID);
                affectedRows = DBManager.ExecuteNonQuery(cmd);
            }
            return affectedRows;
        }

        public static int UserUpdateByUserID(Users.UsersRow ur)
        {
            int ar = 0;
            using (SqlCommand cmd = DBManager.GetCommand("UserUpdateByUserID"))
            {
                cmd.Parameters.AddWithValue("UserID", ur.UserID);
                cmd.Parameters.AddWithValue("UserName", ur.UserName);
                cmd.Parameters.AddWithValue("Password", ur.Password);
                cmd.Parameters.AddWithValue("FullName", ur.FullName);
                cmd.Parameters.AddWithValue("UserType", ur.UserType);
                ar = DBManager.ExecuteNonQuery(cmd);
            }
            return ar;
        }

        public static bool UserAlreadyExists(string qUser)
        {
            Users.UsersDataTable table = new Users.UsersDataTable();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE UserName = '" + qUser + "'"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(table, cmd);
            }

            if (table.Rows.Count == 0)
                return false;
            else
                return true;
        }
    }
}
